package jdbcdemo2.jdbc.dao.iml;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import javabean.Users;
import jdbcdemo2.jdbc.DBHelper;
import jdbcdemo2.jdbc.dao.IUsersDao;

public class IUsersdaoIml implements IUsersDao {

	@Override
	public boolean add(Users para) {
		Connection conn =DBHelper.openConnection();
		PreparedStatement stmt = null;
		try {
			String sql = "insert into users (id,name,pwd,email,birthday) values(SE_USERS.nextval,?,?,?,?)";
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, para.getName());
			stmt.setString(2, para.getPwd());
			stmt.setString(3, para.getEmail());
			stmt.setTimestamp(4, new java.sql.Timestamp(para.getBirthday().getTime()));
			int count = stmt.executeUpdate();
			if (count == 1) {
				return true;
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBHelper.closeAll(null, stmt, conn);
		}

		return false;
	}

	@Override
	public boolean edit(Users para) {
		Connection conn = null;
		conn = DBHelper.openConnection();
		PreparedStatement stmt = null;

		try {
			String sql = "update users set name=?,pwd=?,email=?,birthday=? where id=? ";
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, para.getName());
			stmt.setString(2, para.getPwd());
			stmt.setTimestamp(3, new java.sql.Timestamp(para.getBirthday().getTime()));
			stmt.setInt(4, para.getId());
			int count = stmt.executeUpdate(sql);
			if (count == 1) {
				return true;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBHelper.closeAll(null, stmt, conn);
		}

		return false;
	}

	@Override
	public boolean deleteById(int id) {
		Connection conn = null;
		conn = DBHelper.openConnection();
		PreparedStatement stmt = null;

		try {
			String sql = "delete from users where id=?";
			stmt = conn.prepareStatement(sql);
			stmt.setInt(1, id);
			int count = stmt.executeUpdate();
			if (count == 1) {
				return true;
			}
		} catch (SQLException e) {

			e.printStackTrace();
		} finally {
			DBHelper.closeAll(null, stmt, conn);
		}

		return false;
	}

	@Override
	public List<Users> findAll() {
		List<Users> list = new ArrayList<Users>();
		Connection conn = null;
		conn = DBHelper.openConnection();
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			String sql = "select id,name,pwd,email,birthday from users";
			stmt = conn.prepareStatement(sql);
			rs = stmt.executeQuery();
			while (rs.next()) {
				int id = rs.getInt(1);
				String name = rs.getString("name");
				String pwd = rs.getString("pwd");
				String email = rs.getString("email");
				Date date = rs.getTimestamp("birthday");
				Users users = new Users(id, name, pwd, email, date);
				list.add(users);

			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBHelper.closeAll(rs, stmt, conn);
		}

		return list;
	}

	@Override
	public List<Users> findByExample(Users para) {
		List<Users> list = new ArrayList<Users>();
		Connection conn = null;
		conn = DBHelper.openConnection();
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			String sql = "select * from " + "(select id,name,pwd,email,birthday from users where name like %?%)";
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, para.getName());
			rs = stmt.executeQuery();
			while (rs.next()) {
				int id = rs.getInt(1);
				String name = rs.getString("name");
				String pwd = rs.getString("pwd");
				String email = rs.getString("email");
				Date date = rs.getTimestamp("birthday");
				Users users = new Users(id, name, pwd, email, date);
				list.add(users);

			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DBHelper.closeAll(rs, stmt, conn);
		}

		return list;
	}

	@Override
	public int addAll(List<Users> users) {
		int result = 0;
		Connection conn = DBHelper.openConnection();
		PreparedStatement stmt = null;
		try {
			conn.setAutoCommit(false);
			String sql = "insert into users(id,name,pwd,email,birthday) values(SE_USERS.NEXTVAL,?,?,?,?)";
			stmt = conn.prepareStatement(sql);
//			System.out.println(users.size());
//			for(Users users2:users) {
//				System.out.println(users2.getName());
//			}
//			
			for (Users users2 : users) {
//				stmt = conn.prepareStatement(sql);    这个问题
				stmt.setString(1, users2.getName());
				stmt.setString(2, users2.getPwd());
				stmt.setString(3, users2.getEmail());
				stmt.setTimestamp(4, new java.sql.Timestamp(users2.getBirthday().getTime()));
				stmt.addBatch();
			}
			int [] count = stmt.executeBatch();//[-2]
//			System.out.println(count.length);
			for (int i : count) {
				if (i == -2) {
					result++;
				}
			}
			if (result == users.size()) {
				conn.commit();
				conn.setAutoCommit(true);
			} else {
				conn.rollback();
			}
		} catch (SQLException e) {

			e.printStackTrace();
			try {
				conn.rollback();
				conn.setAutoCommit(true);
			} catch (SQLException e1) {

				e1.printStackTrace();
			}
		} finally {
			DBHelper.closeAll(null, stmt, conn);
		}

		return result;
	}

	@Override
	public int editAll(List<Users> users) {
		int result = 0;
		Connection connection = null;
		connection = DBHelper.openConnection();
		PreparedStatement stmt = null;

		try {
			String sql = "update users set name=?,pwd=?,email=?,birthday=? where id=? ";
			stmt = connection.prepareStatement(sql);
			connection.setAutoCommit(false);
			for (Users users2 : users) {
				stmt.setString(1, users2.getName());
				stmt.setString(2, users2.getPwd());
				stmt.setTimestamp(3, new java.sql.Timestamp(users2.getBirthday().getTime()));
				stmt.setInt(4, users2.getId());
				stmt.addBatch();
			}
			int[] count = stmt.executeBatch();
			for (int i : count) {
				if (i == 1) {
					result++;
				}
				if (result == users.size()) {
					connection.commit();
					connection.setAutoCommit(true);
				} else {
					connection.rollback();
					connection.setAutoCommit(true);
				}
			}

		} catch (SQLException e) {
			e.printStackTrace();
			try {
				connection.rollback();
				connection.setAutoCommit(true);
			} catch (SQLException e1) {

				e1.printStackTrace();
			}
		}

		return result;
	}

	@Override
	public int deletAll(int[] ids) {
		int result = 0;
		Connection connection = null;
		connection = DBHelper.openConnection();
		PreparedStatement stmt = null;
		String sql = "delete from users where id=?";
		try {
			stmt = connection.prepareStatement(sql);
			connection.setAutoCommit(false);
			for (int i : ids) {
				stmt.setInt(1, i);
				stmt.addBatch();
			}
			int[] count = stmt.executeBatch();
			for (int i : count) {
				if (i==1) {
					result++;
				}
			}if (result==ids.length) {
				connection.commit();
				connection.setAutoCommit(true);
			}else {
				connection.rollback();
				connection.setAutoCommit(true);
			}
		} catch (SQLException e) {
			e.printStackTrace();
			try {
				connection.rollback();
				connection.setAutoCommit(true);
			} catch (SQLException e1) {
				
				e1.printStackTrace();
			}
		}

		return result;
	}

	@Override
	public List<Users> findBypage(int page, int size) {
		List<Users> list = new ArrayList<Users>();
		Connection connection =null;
		connection = DBHelper.openConnection();
		PreparedStatement stmt = null;
		ResultSet rs =null;
		try {
			String sql = "select * from"+" (select rownum rn,id,name,pwd,email,birthday from users where rownum<=?*?) where rn>(?-1)*?";
			stmt = connection.prepareStatement(sql);
			stmt.setInt(1, page);
			stmt.setInt(2, size);
			stmt.setInt(3, page);
			stmt.setInt(4, page);
			
			rs = stmt.executeQuery();
			while (rs.next()) 
			{
			int id = rs.getInt("ID");
			String name = rs.getString("name");
			String pwdString = rs.getString("pwd");
			String emailString = rs.getString("email");
			Date date = rs.getTimestamp("birthday");
			Users users = new Users(id,name,pwdString,emailString,date);
			list.add(users);
		}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			DBHelper.closeAll(rs, stmt, connection);
		}
		
		return list;
	}

}
